﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace SharpOffice
{
    class SQL
    {
        /// <summary>
        /// This is class for all SQLite operations
        /// </summary>
        private SQLiteConnection sql_con = null;        //connection object
        private SQLiteCommand sql_cmd = null;           // query executing object
        private SQLiteCommandBuilder cmd_build = null;  // object to build queryes
        private SQLiteDataAdapter db = null;            // Serves as a bridge between a DataSet and database for retrieving and saving data.
        private DataSet ds = null;                      // Dataset where the retrieved data is stored briefly
        private DataTable dt = null;                    //Formating dataset data to table format

        private String create_table = String.Empty;        
        private String select = String.Empty;
        
        
        
        /// <summary>
        /// Opens the connection to customer database.
        /// </summary>
        /// <returns>Open connection</returns>
        private SQLiteConnection getConnection()
        {
            sql_con = new SQLiteConnection("Data Source=customers.db;Version=3;Integrated Security=TRUE");
            sql_con.Open();
            return sql_con;
        }

        /// <summary>
        /// Creates the customers table if the table doesn't exist.
        /// </summary>
        public void createTable()
        {
            create_table = "CREATE TABLE IF NOT EXISTS customers"
                                + "(cust_ID INTEGER PRIMARY KEY                 AUTOINCREMENT, "
                                + "first_name   TEXT                    NOT NULL, "
                                + "surname              TEXT                    NOT NULL, " 
                                + "address          CHAR(50)                , "
                                + "postcode             CHAR(5)                 , " 
                                + "postarea                 CHAR(25)                , "
                                + "phone                CHAR(50)                , " 
                                + "email            CHAR(50)                , "
                                + "lastvisit    DATETIME)";
            try
            {
                sql_cmd = new SQLiteCommand(create_table, getConnection());
                sql_cmd.ExecuteNonQuery();
                
            }
            catch(SQLiteException e)
            {
                Console.WriteLine(e.ErrorCode);
            }
            getConnection().Close();
        } 

        

        /// <summary>
        /// Fetch requested data from database
        /// </summary>
        /// <returns>DataTable from database</returns>
        public DataTable selectQuery()
        {
            select = @"SELECT * FROM customers";
            try
            {
                
                sql_cmd = new SQLiteCommand(select, getConnection());
                db = new SQLiteDataAdapter(sql_cmd);
                cmd_build = new SQLiteCommandBuilder(db);
                ds = new DataSet(); 
                //ds.Reset();
                db.Fill(ds,"customers");
                dt = ds.Tables["customers"];
                
               
               
            }
            catch (SQLiteException e)
            {

                Console.WriteLine(e.ErrorCode);
            }


            getConnection().Close();
            return dt;
        }

        /// <summary>
        /// Updates the dataset after changes (in datagridview), and updates the changes to database via SQLiteDataAdapter.
        /// </summary>
        /// <returns>SQLiteDataAdapter object with Update function</returns>
        public SQLiteDataAdapter updateData()
        {
            db.Update(ds, "customers");
            return db;
        } 
    }
}
